﻿CREATE FUNCTION [RestartFramework].[GetETLJobStageVisualisation]
(
	@pETLJobName	SYSNAME
)
RETURNS TABLE AS RETURN
(
	SELECT	[ETLJobStageRank]	=	REPLICATE('  ',q.[JobStageOrderRank]) + '|-' + q.[ETLJobStageName]
	,		q.[ETLJobStageOrder]
	,		q.[ETLJobStageEnabled]
	,       q.[ETLJobStageIsComplete]
	FROM	(
			SELECT	ejs.[ETLJobStageName]
			,		[JobStageOrderRank] =	DENSE_RANK() OVER (ORDER BY ejs.[ETLJobStageOrder] ASC) - 1
			,		ejs.[ETLJobStageOrder]
			,		ejs.[ETLJobStageEnabled]
			,       ejs.[ETLJobStageIsComplete]
			FROM	[RestartFramework].[ETLJob] ej
			INNER JOIN [RestartFramework].[ETLJobStage] ejs		ON	ej.[ETLJobId]		=	ejs.[ETLJobId]
			WHERE	ej.[ETLJobName] = @pETLJobName
	)q
)
